package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.OrdersDao;
import com.foreknow.elm.po.Business;
import com.foreknow.elm.po.Orders;
import com.foreknow.elm.util.CommonUtil;
import com.foreknow.elm.util.DBUtil;
import com.sun.org.apache.xpath.internal.operations.Or;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author shkstart
 * @create 2021-08-27 11:42
 */
public class OrdersDaoImpl implements OrdersDao {

    private Connection con;
    private PreparedStatement pst;
    private ResultSet rs;
    int result = 0;

//    @Override
//    public int saveOrders(Orders orders) throws SQLException {
//        String sql = "insert into orders values(null,?,?,?,?,?,0)";
//
//
//        try {
//            con = DBUtil.getConnection();
//            //设置返回自增长列值
//            pst = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
//            pst.setString(1, orders.getUserId());
//            pst.setInt(2, orders.getBusinessId());
//            pst.setString(3, CommonUtil.getCurrentDate());
//            pst.setDouble(4, orders.getOrderTotal());
//            pst.setInt(5, orders.getDaId());
//            pst.executeUpdate();
//
//
//            //获取自增长列值
//            rs = pst.getGeneratedKeys();
//            if (rs.next()) {
//                result = rs.getInt(1);
//            }
//
//
//        } finally {
//            DBUtil.close(pst);
//        }
//        return result;
//    }
/////**
// * 然后根据用户编号、商家编号从购物车表中查询所有数据，批量添加到订单明细表中，
// */
//    @Override
//    public int  saveTailet(int result,int quantity) throws SQLException{
//        StringBuffer sql1=new StringBuffer();
//        sql1.append("INSERT INTO orderdetailet VALUES(null,");
//        //orderId就是result
//        sql1.append(result+",");
//        //返回 foodId的值
//        int foodId=0;
//        StringBuffer sql2=new StringBuffer();
//        sql2.append("SELECT foodId FROM orderdetailet WHERE orderId=");
//        sql2.append(result);
//        ResultSet resultSet = pst.executeQuery(String.valueOf(sql2));
//        while (resultSet.next()){
//            foodId=resultSet.getInt("foodId");
//        }
//        sql1.append(foodId+",");
//        //quantity 的值
//        sql1.append("?)");
//        int i=0;
//        try {
//            con = DBUtil.getConnection();
//            pst=con.prepareStatement(String.valueOf(sql1));
//            pst.setInt(1,quantity);
//          i = pst.executeUpdate();
//
//        }finally {
//            DBUtil.close(rs,pst);
//        }
//
//        return i;
//    }
//
//    /**
//     * 然后根据用户编号、商家编号删除购物车表中的数据。
//     * @param userId
//     * @param businessId
//     * @return
//     * @throws SQLException
//     */
//    @Override
//    public int delOrders(int userId, int businessId) throws SQLException {
//        StringBuffer sql1=new StringBuffer();
//        sql1.append("delete from orders where userId=? and businessId=?");
//        //orderId就是result
//        sql1.append(result+",");
//        //返回 foodId的值
//        int foodId=0;
//        StringBuffer sql2=new StringBuffer();
//        sql2.append("SELECT foodId FROM orderdetailet WHERE orderId=");
//        sql2.append(result);
//        ResultSet resultSet = pst.executeQuery(String.valueOf(sql2));
//        while (resultSet.next()){
//            foodId=resultSet.getInt("foodId");
//        }
//        sql1.append(foodId+",");
//        //quantity 的值
//        sql1.append("?)");
//        return 0;
//    }


    /**
     * 1. OrdersController/createOrders
     * 参数：userId、businessId、daId、orderTotal
     * 返回值：int（订单编号）
     * 功能：根据用户编号、商家编号、订单总金额、送货地址编号向订单表中添加一条记录，
     * 并获取自动生成的订单编号，
     * 然后根据用户编号、商家编号从购物车表中查询所有数据，批量添加到订单明细表中，
     * 然后根据用户编号、商家编号删除购物车表中的数据。
     */
    @Override
    public int saveOrders(String userId, int businessId, int daId, double orderTotal) throws SQLException {
        Orders orders = new Orders();
        int orderId = 0;
        int foodId = 0;
        int quantity = 0;
//
//        String sql = " SELECT *  FROM orders o,business b,orderdetailet t WHERE o.businessId=b.businessId AND o.orderId=t.orderId  AND o.userId=? AND O.businessId=?";
//
//        con = DBUtil.getConnection();
//        pst = con.prepareStatement(sql);
//        pst.setString(1, userId);
//        pst.setInt(2, businessId);
//        rs = pst.executeQuery();
//        while (rs.next()) {
//            orderId = rs.getInt("orderId");
//            foodId = rs.getInt("foodId");
//            quantity = rs.getInt("quantity");
//
//        }


        // 功能：根据用户编号、商家编号、订单总金额、送货地址编号向订单表中添加一条记录，
        //              并获取自动生成的订单编号，

        String sql1 = "insert into orders values(null,?,?,?,?,?,0)";

        try {
            con = DBUtil.getConnection();
            //设置返回自增长列值
            pst = con.prepareStatement(sql1, PreparedStatement.RETURN_GENERATED_KEYS);
            pst.setString(1, userId);
            pst.setInt(2, businessId);
            pst.setString(3, CommonUtil.getCurrentDate());
            pst.setDouble(4, orderTotal);
            pst.setInt(5, daId);
            pst.executeUpdate();
            //获取自增长列值
            rs = pst.getGeneratedKeys();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } finally {
            DBUtil.close(pst);
        }


        // 然后根据用户编号、商家编号从购物车表中查询所有数据，批量添加到订单明细表中，


//        String sql2 = "INSERT INTO orderdetailet VALUES(null,?,?,?)";

//
//        pst = con.prepareStatement(sql2);
//        pst.setInt(1, orderId);
//        pst.setInt(2, foodId);
//        pst.setInt(3, quantity);
//        int row=pst.executeUpdate();

//        /**
//         * 然后根据用户编号、商家编号删除购物车表中的数据。
//         */
//        String sql3="delete from cart where userId =? and businessId=?";
//        pst = con.prepareStatement(sql3);
//        pst.setString(1,userId);
//        pst.setInt(2,businessId);
//        int row1= pst.executeUpdate();


        return result;
    }

    /**
     * 2. OrdersController/getOrdersById
     * 参数：orderId
     * 返回值：orders对象（包括多对一：商家信息；    一对多：订单明细信息）
     * 功能：根据订单编号查询订单信息，包括所属商家信息，和此订单的所有订单明细信息
     **/
    @Override
    public Orders getOrdersById(int orderId) throws SQLException {
        Orders orders = new Orders();
//        String sql = " SELECT *  FROM orders o,business b,orderdetailet t WHERE o.businessId=b.businessId AND o.orderId=t.orderId  AND o.orderId=?";
        StringBuffer sql = new StringBuffer();
        sql.append(" select o.*, ");
        sql.append("        b.businessId bbusinessId, ");
        sql.append("        b.businessName bbusinessName, ");
        sql.append("        b.deliveryPrice bdeliveryPrice ");
        sql.append(" from orders o left join business b on o.businessId=b.businessId ");
        sql.append(" where o.orderId=? ");
        //获取连接
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(String.valueOf(sql));
            pst.setInt(1, orderId);
            rs = pst.executeQuery();
            while (rs.next()) {
//                orders.setOrderId(rs.getInt("orderId"));
//                orders.setUserId(rs.getString("userId"));
//                orders.setBusinessId(rs.getInt("businessId"));
//                orders.setOrderDate(rs.getString("orderDate"));
//                orders.setOrderTotal(rs.getDouble("orderTotal"));
//                orders.setDaId(rs.getInt("daId"));
//                orders.setOrderState(rs.getInt("orderState"));
//                Business business = new Business();
//                business.setBusinessId(rs.getInt("businessId"));
//                business.setBusinessName(rs.getString("businessName"));
//                business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
//                orders.setBusiness(business);
                orders = new Orders();
                orders.setOrderId(rs.getInt("orderId"));
                orders.setUserId(rs.getString("userId"));
                orders.setBusinessId(rs.getInt("businessId"));
                orders.setOrderDate(rs.getString("orderDate"));
                orders.setOrderTotal(rs.getDouble("orderTotal"));
                orders.setOrderState(rs.getInt("orderState"));
                Business business = new Business();
                business.setBusinessId(rs.getInt("bbusinessId"));
                business.setBusinessName(rs.getString("bbusinessName"));
                business.setDeliveryPrice(rs.getDouble("bdeliveryPrice"));
                orders.setBusiness(business);
            }


        } finally {
            DBUtil.close(rs, pst);
        }
        return orders;
    }

    /**
     * 3. OrdersController/listOrdersByUserId
     * 参数：userId
     * 返回值：orders数组（包括多对一：商家信息；    一对多：订单明细信息）
     * 功能：根据用户编号查询此用户的所有订单信息
     */
    @Override
    public List<Orders> listOrdersByUserId(String userId) throws SQLException {
        List<Orders> list = new ArrayList<>();

//        String sql = "select o.*,b.businessId,b.businessName,b.businessName,b.deliveryPrice,b.deliveryPrice from orders o,business b where o.businessId=b.businessId AND o.userId=?";
        StringBuffer sql = new StringBuffer();
        sql.append(" select o.*, ");
        sql.append("        b.businessId bbusinessId, ");
        sql.append("        b.businessName bbusinessName, ");
        sql.append("        b.deliveryPrice bdeliveryPrice ");
        sql.append(" from orders o left join business b on o.businessId=b.businessId ");
        sql.append(" where o.userId=?");
        con = DBUtil.getConnection();
        pst = con.prepareStatement(sql.toString());
        pst.setString(1, userId);
        rs = pst.executeQuery();
        while (rs.next()) {
//            Orders o = new Orders();
//            o.setOrderId(rs.getInt("orderId"));
//            o.setUserId(rs.getString("userId"));
//            o.setBusinessId(rs.getInt("businessId"));
//            o.setOrderDate(rs.getString("orderDate"));
//            o.setOrderTotal(rs.getDouble("orderTotal"));
//            o.setDaId(rs.getInt("daId"));
//            o.setOrderState(rs.getInt("orderState"));
//            Business business = new Business();
//            business.setBusinessId(rs.getInt("businessId"));
//            business.setBusinessName(rs.getString("businessName"));
//            business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
//            o.setBusiness(business);
//            list.add(o);
            Orders orders = new Orders();
            orders.setOrderId(rs.getInt("orderId"));
            orders.setUserId(rs.getString("userId"));
            orders.setBusinessId(rs.getInt("businessId"));
            orders.setOrderDate(rs.getString("orderDate"));
            orders.setOrderTotal(rs.getDouble("orderTotal"));
            orders.setDaId(rs.getInt("daId"));
            orders.setOrderState(rs.getInt("orderState"));
            Business business = new Business();
            business.setBusinessId(rs.getInt("bbusinessId"));
            business.setBusinessName(rs.getString("bbusinessName"));
            business.setDeliveryPrice(rs.getDouble("bdeliveryPrice"));
            orders.setBusiness(business);
            list.add(orders);
        }
        return list;
    }
}
